const Router = require('koa-router');
const router = new Router();
const tool = require('../../tools/tool')
const {db, QueryTypes} = require("../../models/db");
const tools = require("../../tools/tool");

router.post('/home/middle', async ctx => {
    try {
        let tenantId = ctx.user.tenantId;
        let body = ctx.request.body;
        let billType = body.billType
        let range = body.range
        let phsort = body.phsort
        if(billType && range) {
            let beginDate = ''
            let endDate = ''
            if(range === '近一周'){
                endDate = tool.getDay('')
                beginDate = tool.getDay('', -7)
            }
            if(range === '近30天'){
                endDate = tool.getDay('')
                beginDate = tool.getDay(tool.dayjs().subtract(30, 'day'))
            }
            if(range === '近半年'){
                endDate = tool.getDay('',  )
                beginDate = tool.getDay(tool.dayjs().subtract(6, 'month'))
            }
            if(range === '近一年'){
                endDate = tool.getDay('',  )
                beginDate = tool.getDay(tool.dayjs().subtract(1, 'year'))
            }
            let bt = 'SALE'
            if(billType==='购货') bt = 'PUR'

            let sql = ''
            let counts = 7
            switch (range) {
                case '近一周': {
                    counts = 7
                     sql =   `select billDate,transType,sum(isnull(totalAmountReal,0)) totalAmountReal from (
                                    select billDate,case when transType=150601 or transType=150603 then N'销售'
                                   when transType=150602 then N'退货' else N'采购' end transType,totalAmountReal     from
                                    v_main where tenantId=${tenantId} and billType in('PUR','SALE') and 
                                                  billDate between '${beginDate}' and '${endDate}'  and billState='accounted' 
                                            and billType='SALE'
                    ) a  group by billDate,transType`
                    let list = await db.query(sql, {type: QueryTypes.SELECT})
                    let ret = []
                    for (let i = 0; i < counts; i++) {
                        let day = tool.getDay('', -(counts-i-1))
                        let row = {
                            日期: day,
                            销售: 0,
                            退货: 0,
                        }
                        list.map(res => {
                            if(res.billDate === day) {
                                if(res.transType === '销售') {
                                    row.销售 = res.totalAmountReal
                                }
                                if(res.transType === '退货') {
                                    row.退货 = -res.totalAmountReal
                                }
                            }
                        })
                        ret.push(row)
                    }
                    // 单品销售金额
                    let gs = `select top 20 invName,total from (select b.invName,sum(b.amount) total from v_main a inner join v_main_info b 
    on a.tenantId=b.tenantId and a.id=b.iid and a.billState='accounted'
                        and a.billDate between  '${beginDate}' and '${endDate}' and a.transType=150601 where a.tenantId=${tenantId} and b.amount<>0
                                             group by b.invName
                         ) a  order by total ${phsort}   `
                    let ph = await db.query(gs, {type: QueryTypes.SELECT})
                    ph.map(row => {
                        row.total = tool.xe.toFixed(row.total,2)
                    })
                    return ctx.suc('',{
                        chart: ret,
                        ph
                    })
                }
                case '近30天': {
                    counts = 30
                    sql =   `select billDate,transType,sum(isnull(totalAmountReal,0)) totalAmountReal from (
                                    select billDate,case when transType=150601 or transType=150603 then N'销售'
                                   when transType=150602 then N'退货' else N'采购' end transType,totalAmountReal     from
                                    v_main where tenantId=${tenantId} and billType in('PUR','SALE') and 
                                                  billDate between '${beginDate}' and '${endDate}'  and billState='accounted' 
                                            and billType='SALE'
                    ) a  group by billDate,transType`
                    let list = await db.query(sql, {type: QueryTypes.SELECT})
                    let ret = []
                    for (let i = 0; i < counts; i++) {
                        let day = tool.getDay('', -(counts-i-1))
                        let row = {
                            日期: day,
                            销售: 0,
                            退货: 0,
                        }
                        list.map(res => {
                            if(res.billDate === day) {
                                if(res.transType === '销售') {
                                    row.销售 = res.totalAmountReal
                                }
                                if(res.transType === '退货') {
                                    row.退货 = -res.totalAmountReal
                                }
                            }
                        })
                        ret.push(row)
                    }
                    // 单品销售金额
                    let gs = `select top 20 invName,total from (select b.invId,b.invName,sum(b.amount) total from v_main a inner join v_main_info b on 
    a.tenantId=b.tenantId and a.id=b.iid and a.billState='accounted'
                        and a.billDate between  '${beginDate}' and '${endDate}' and a.transType=150601 where a.tenantId=${tenantId} and b.amount<>0
                                             group by b.invId, b.invName
                         ) a  order by total  ${phsort}    `
                    let ph = await db.query(gs, {type: QueryTypes.SELECT})
                    ph.map(row => {
                        row.total = tool.xe.toFixed(row.total,2)
                    })
                    return ctx.suc('',{
                        chart: ret,
                        ph
                    })
                }
                case '近一年': {
                    counts = 12
                    sql =   `select billDate,transType,sum(isnull(totalAmountReal,0)) totalAmountReal from (
                                    select substring(billDate,1,7) billDate,case when transType=150601 or transType=150603 then N'销售'
                                   when transType=150602 then N'退货' else N'采购' end transType,totalAmountReal     from
                                    v_main where tenantId=${tenantId} and billType in('PUR','SALE') and 
                                                  billDate between '${beginDate}' and '${endDate}'  and billState='accounted' 
                                            and billType='SALE'
                    ) a  group by billDate,transType`
                    let list = await db.query(sql, {type: QueryTypes.SELECT})
                    let ret = []
                    for (let i = 0; i < counts; i++) {
                        let day = tool.getDay(tool.dayjs().subtract((counts-i-1), 'month')).substring(0, 7)
                        let row = {
                            日期: day,
                            销售: 0,
                            退货: 0,
                        }
                        list.map(res => {
                            if(res.billDate === day) {
                                if(res.transType === '销售') {
                                    row.销售 = res.totalAmountReal
                                }
                                if(res.transType === '退货') {
                                    row.退货 = -res.totalAmountReal
                                }
                            }
                        })
                        ret.push(row)
                    }
                    // 单品销售金额
                    let gs = `select top 20 invName,total from (select b.invId,b.invName,sum(b.amount) total from v_main a inner join v_main_info 
    b on a.tenantId=b.tenantId and a.id=b.iid and a.billState='accounted'
                        and a.billDate between  '${beginDate}' and '${endDate}' and a.transType=150601 where a.tenantId=${tenantId} and b.amount<>0
                                             group by b.invId, b.invName
                         ) a  order by total  ${phsort}    `
                    let ph = await db.query(gs, {type: QueryTypes.SELECT})
                    ph.map(row => {
                        row.total = tool.xe.toFixed(row.total,2)
                    })
                    return ctx.suc('',{
                        chart: ret,
                        ph
                    })
                }
            }

            return ctx.suc('', list)
        }
        else {
            return ctx.err('没有选择条件')
        }

    } catch (e) {
        return ctx.err(e);
    }
})


module.exports = router;
